package Playlist
{
	import FileSearcher.itemsRenderers.MusicRenderer;
	
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	import spark.primitives.Path;
	
	
	
	public class SQLPlaylist
	{
	
		private var databaseFile:File = null;
		
		public function SQLPlaylist()
		{
			databaseFile = new File("C:\\Music\\db.sqlite");
			if (!databaseFile.exists)
			{
				createDataBase();
			}
		}

		public function createDataBase() : void
		{
			var Connection:SQLConnection = new SQLConnection();
			var Statement:SQLStatement = new SQLStatement();
			Connection.open(databaseFile, SQLMode.CREATE);
			Statement.sqlConnection = Connection;
			
			var sQuery:String = "CREATE TABLE music (";
			sQuery += "id_music INTEGER PRIMARY KEY, ";
			sQuery += "path VARCHAR(512),";
			sQuery += "id_playlist INTEGER";
			sQuery += ")";
			Statement.text = sQuery;
			Statement.execute();
			
			
			sQuery = "CREATE TABLE playlist (";
			sQuery += "id_playlist INTEGER PRIMARY KEY, ";
			sQuery += "name VARCHAR(64)";
			sQuery += ")";
			Statement.text = sQuery;
			Statement.execute();
			
			
			Connection.close();
		}
		
		public function createPlaylist(name:String) : Number
		{
			var Connection:SQLConnection = new SQLConnection();
			var Statement:SQLStatement = new SQLStatement();
			Connection.open(databaseFile, SQLMode.UPDATE);
			Statement.sqlConnection = Connection;
			
			var sQuery:String = "INSERT INTO playlist (name) VALUES (@name)";
			Statement.text = sQuery;
			Statement.parameters["@name"] = name;
			Statement.itemClass = db_playlist;
			Statement.execute();
			Connection.close();
			return Statement.getResult().lastInsertRowID;
		}
		
		public function getPlaylistIdFromName(name:String) : Number
		{
			var Connection:SQLConnection = new SQLConnection();
			var Statement:SQLStatement = new SQLStatement();
			Connection.open(databaseFile, SQLMode.UPDATE);
			Statement.sqlConnection = Connection;
			
			var sQuery:String = "SELECT * FROM playlist WHERE name = @name";
			Statement.text = sQuery;
			Statement.parameters["@name"] = name;
			Statement.itemClass = db_playlist;
			Statement.execute();
			var r:SQLResult = Statement.getResult();
			for each(var p:db_playlist in r.data)
			{
				return p.id_playlist;
			}
			Connection.close();
			return Statement.getResult().lastInsertRowID;
		}
		
		public function createMusic(path:String, id_playlist:Number) : Number
		{
			var Connection:SQLConnection = new SQLConnection();
			var Statement:SQLStatement = new SQLStatement();
			Connection.open(databaseFile, SQLMode.UPDATE);
			Statement.sqlConnection = Connection;
			
			var sQuery:String = "INSERT INTO music (path, id_playlist) VALUES (@path, @id_playlist)";
			Statement.text = sQuery;
			Statement.parameters["@path"] = path;
			Statement.parameters["@id_playlist"] = id_playlist;
			Statement.itemClass = db_playlist;
			Statement.execute();
			Connection.close();
			return Statement.getResult().lastInsertRowID;
		}
		
		public function deleteMusic(path:String) : void
		{
			var Connection:SQLConnection = new SQLConnection();
			var Statement:SQLStatement = new SQLStatement();
			Connection.open(databaseFile, SQLMode.UPDATE);
			Statement.sqlConnection = Connection;
			
			var sQuery:String = "DELETE FROM music WHERE path = @path";
			Statement.text = sQuery;
			Statement.parameters["@path"] = path;
			Statement.execute();
			Connection.close();
		}

		public function loadFromDataBase() : ArrayCollection
		{
			var playlists:ArrayCollection = new ArrayCollection();
			
			var Connection:SQLConnection = new SQLConnection();
			var Statement_pl:SQLStatement = new SQLStatement();
			var Statement_mu:SQLStatement = new SQLStatement();
			Connection.open(databaseFile, SQLMode.READ);
			Statement_pl.sqlConnection = Connection;
			Statement_mu.sqlConnection = Connection;
			
			var sQuery:String = "SELECT * FROM playlist";
			Statement_pl.text = sQuery;
			Statement_pl.itemClass = db_playlist;
			Statement_pl.execute();
			var r:SQLResult = Statement_pl.getResult();
			for each(var p:db_playlist in r.data)
			{
				var playlist:Playlist = new Playlist();
				playlist.db_id = p.id_playlist; 
				sQuery = "SELECT * FROM music WHERE id_playlist = @id_playlist";
				Statement_mu.text = sQuery;
				Statement_mu.parameters["@id_playlist"] = p.id_playlist;
				Statement_mu.itemClass = db_music;
				Statement_mu.execute();
				var rr:SQLResult = Statement_mu.getResult();
				for each(var m:db_music in rr.data)
				{
					playlist.AddSong(m.path);
				}
				playlists.addItem(playlist);
			}
			Connection.close();
			return playlists;
		}
		
		/*public function saveIntoDataBase(playlists:ArrayCollection) : void
		{
			for each (var playlist:Playlist in playlists)
			{
				var id_playlist:Number = createPlaylist("playlist");
				for (var i:int = 0; i < playlist.length(); ++i)
				{
					createMusic(playlist.getsongName(i), id_playlist);
				}
			}
		}*/
	}
}